﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;


public partial class S_CountPage : System.Web.UI.Page
{
    public static DataTable ta = new DataTable(); //用于存储从数据库中取出来的表格，可以在多个函数中使用，所以使用static
    public static DataView vi = new DataView();   //用于数据绑定
    public static string accnum;           //登录账号
    public static string role;             //登录身份

    protected void Page_Load(object sender, EventArgs e)
    {
        btnFirst.Text = "最首页";                 //分页处理
        btnPrev.Text = "前一页";
        btnNext.Text = "下一页";
        btnLast.Text = "最后页";

        if (!IsPostBack)
        {
            accnum = (string)Session["AccNum"];
            role = (string)Session["role"];
            string sql = "select courseid,COURSE_INFO.name as coursename,COURSE_INFO.credit as credit,studentid,STU_INFO.name as studentname,mark,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1';";
            Execute(sql);
        }
    }

    private void ShowStats()                     //用于分页处理
    {
        lblCurrentIndex.Text = "第 " + (dg1.CurrentPageIndex + 1).ToString() + " 页";
        lblPageCount.Text = "总共 " + dg1.PageCount.ToString() + " 页";
    }

    public void PagerButtonClick(object sender, EventArgs e) //用于分页处理
    {
        string arg = ((LinkButton)sender).CommandArgument.ToString();
        switch (arg)
        {
            case "next":
                if (dg1.CurrentPageIndex < (dg1.PageCount - 1))
                {
                    dg1.CurrentPageIndex += 1;
                }
                break;
            case "prev":
                if (dg1.CurrentPageIndex > 0)
                {
                    dg1.CurrentPageIndex -= 1;
                }
                break;
            case "last":
                dg1.CurrentPageIndex = (dg1.PageCount - 1);
                break;
            default:
                dg1.CurrentPageIndex = System.Convert.ToInt32(arg);
                break;
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();
    }

    public void MyDataGrid_Page(Object source, DataGridPageChangedEventArgs e)  //用于分页处理
    {
        int startIndex;
        startIndex = dg1.CurrentPageIndex * dg1.PageSize;
        dg1.CurrentPageIndex = e.NewPageIndex;
        //BindGrid();
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();

    }

    protected void LinkButton2_Click(object sender, EventArgs e)//信息统计
    {
        DL1.Text = "请选择学期";
        DL2.Text="请选择学年";
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
    }

    void Execute(string sql)//缺少查询信息不存在的出错处理，即返回空表，（上面的执行语句都可用此函数替代）
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ta.Columns.Clear();
        ta.Rows.Clear();
        da.Fill(ta);
        vi = new DataView(ta);
        dg1.DataSource = vi;
        dg1.DataBind();
        myConn.Close();
    }
    string ReaderValue(string sql)
    {
        string value = "";
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataReader myReader = cmd.ExecuteReader();
        int i = 0;
        while (myReader.Read())
        {
            value = myReader[0].ToString().Trim();
            i++;
        }
        myReader.Close();
        myConn.Close();
        return value;
    }

    protected void Button1_Click(object sender, EventArgs e)//统计总学分
    {
        string credit = "";
        string sql = "select sum(COURSE_INFO.credit) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1';";
        credit=ReaderValue(sql);
        TextBox1.Text = credit;
    }

    protected void Button2_Click(object sender, EventArgs e)//缺乏错误处理，比如大一上没有课
    {
        if (DL1.Text == "请选择学期")
        {
            Alert("请选择学期");
        }
        else
        {
            double credit = 0;
            double summark = 0;
            string sql = "select sum(COURSE_INFO.credit) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' and CLASS_INFO.state='1';";
            credit = Convert.ToDouble(ReaderValue(sql));
            sql = "select sum(COURSE_INFO.credit*CLASS_INFO.mark) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='" + DL1.Text + "' and CLASS_INFO.state='1';";
            summark = Convert.ToDouble(ReaderValue(sql));
            TextBox2.Text = (summark / credit).ToString();
        }
    }

    protected void Button3_Click(object sender, EventArgs e)
    {
        if (DL1.Text == "请选择学年")
        {
            Alert("请选择学年");
        }
        else
        {
            string sql = "";
            double credit = 0;
            double summark = 0;
            if (DL2.Text == "大一")
            {
                sql = "select sum(COURSE_INFO.credit) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='大一上学期' or SEM_INFO.name='大一下学期' and CLASS_INFO.state='1';";
                credit = Convert.ToDouble(ReaderValue(sql));
                sql = "select sum(COURSE_INFO.credit*CLASS_INFO.mark) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='大一上学期' or SEM_INFO.name='大一下学期' and CLASS_INFO.state='1';";
                summark = Convert.ToDouble(ReaderValue(sql));
            }
            else if (DL2.Text == "大二")
            {
                sql = "select sum(COURSE_INFO.credit) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='大二上学期' or SEM_INFO.name='大二下学期' and CLASS_INFO.state='1';";
                credit = Convert.ToDouble(ReaderValue(sql));
                sql = "select sum(COURSE_INFO.credit*CLASS_INFO.mark) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='大二上学期' or SEM_INFO.name='大二下学期' and CLASS_INFO.state='1';";
                summark = Convert.ToDouble(ReaderValue(sql));
            }
            else if (DL2.Text == "大三")
            {
                sql = "select sum(COURSE_INFO.credit) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='大三上学期' or SEM_INFO.name='大三下学期' and CLASS_INFO.state='1';";
                credit = Convert.ToDouble(ReaderValue(sql));
                sql = "select sum(COURSE_INFO.credit*CLASS_INFO.mark) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='大三上学期' or SEM_INFO.name='大三下学期' and CLASS_INFO.state='1';";
                summark = Convert.ToDouble(ReaderValue(sql));
            }
            else if (DL2.Text == "大四")
            {
                sql = "select sum(COURSE_INFO.credit) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='大四上学期' or SEM_INFO.name='大四下学期' and CLASS_INFO.state='1';";
                credit = Convert.ToDouble(ReaderValue(sql));
                sql = "select sum(COURSE_INFO.credit*CLASS_INFO.mark) from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and STU_INFO.id='"+accnum+"' and CLASS_INFO.semesterid=SEM_INFO.id and SEM_INFO.name='大四上学期' or SEM_INFO.name='大四下学期' and CLASS_INFO.state='1';";
                summark = Convert.ToDouble(ReaderValue(sql));
            }
            TextBox3.Text = (summark / credit).ToString();
        }
    }



    void Alert(string message)//用于测试，无实际意义
    {
        Response.Write("<script>alert('" + message + "')</script>");
    }
    
}
